/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package alwv.reports.pdf;

import alwv.database.DerbyConnection;
import alwv.database.Query;
import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
import java.awt.Desktop;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.math.RoundingMode;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Calendar;
import java.sql.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Brad
 */
public class YearlyReportWithLobbyingExpense {

    private static final float LEFT = 0.25f;
    private static final float RIGHT = 0.25f;
    private static final float TOP = 50.0f;
    private static final float BOTTOM = 50.0f;
    private static final String tab = "    ";

    public static void generateReport(Connection conn, int year, String filename) {
        try {
            Calendar cal = Calendar.getInstance();
            cal.set(year + 1, 5, 30);
            java.util.Date temp = cal.getTime();
            Date date = new Date(temp.getTime());

            Document d = new Document(PageSize.A4.rotate());
            d.setMargins(LEFT, RIGHT, TOP, BOTTOM);

            PdfWriter writer = PdfWriter.getInstance(d, new FileOutputStream(filename));
            d.open();

            PdfPTable table = new PdfPTable(6);
            float[] widths = {85f, 20f, 40f, 40f, 40f, 40f};
            //table.setLockedWidth(true);
            table.setWidths(widths);
            table.setWidthPercentage(85f);
            table.setHeaderRows(1);

            table.addCell(headerCell("Category Description"));
            table.addCell(headerCell("Budget"));
            table.addCell(headerCell("Actual Non-Lobbying"));
            table.addCell(headerCell("Actual Lobbying"));
            table.addCell(headerCell("Actual as of 6/30/" + (year + 1)));
            table.addCell(lobbyingCell(""));

            // Display expense information
            table.addCell(sectionCell("EXPENSES"));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(lobbyingCell(""));

            BigDecimal sectionBudgetTotal = new BigDecimal(0.0);
            sectionBudgetTotal = sectionBudgetTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);
            BigDecimal sectionNonLobbyingTotal = new BigDecimal(0.0);
            sectionNonLobbyingTotal = sectionNonLobbyingTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);
            BigDecimal sectionActualLobbyingTotal = new BigDecimal(0.0);
            sectionActualLobbyingTotal = sectionActualLobbyingTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);
            BigDecimal sectionActualTotal = new BigDecimal(0.0);
            sectionActualTotal = sectionActualTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);

            BigDecimal categoryBudgetTotal = new BigDecimal(0.0);
            categoryBudgetTotal = categoryBudgetTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);
            BigDecimal categoryNonLobbyingTotal = new BigDecimal(0.0);
            categoryNonLobbyingTotal = categoryNonLobbyingTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);
            BigDecimal categoryActualLobbyingTotal = new BigDecimal(0.0);
            categoryActualLobbyingTotal = categoryActualLobbyingTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);
            BigDecimal categoryActualTotal = new BigDecimal(0.0);
            categoryActualTotal = categoryActualTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);

            ArrayList<String> information = Query.getAllExpenseCategoriesAndSubcategories(conn);
            String currentCategory = information.get(0);
            for (int i = 0; i < information.size(); i++) {
                if (Query.isCategory(conn, information.get(i)) && !information.get(i).equals(currentCategory)) {
                    if (Query.hasSubcategory(conn, currentCategory)) {
                        table.addCell(categoryTotalCell("TOTAL " + currentCategory));

                        table.addCell(totalAmountCell(String.valueOf(categoryBudgetTotal)));
                        table.addCell(totalAmountCell(String.valueOf(categoryNonLobbyingTotal)));

                        if (categoryActualLobbyingTotal.doubleValue() > 0.0) {
                            table.addCell(totalAmountCell(String.valueOf(categoryActualLobbyingTotal)));
                        } else {
                            table.addCell(totalAmountCell("-"));
                        }
                        table.addCell(totalAmountCell(String.valueOf(categoryActualLobbyingTotal.add(categoryNonLobbyingTotal))));

                        table.addCell(lobbyingCell(""));
                    }

                    categoryBudgetTotal = new BigDecimal(0.0);
                    categoryBudgetTotal = categoryBudgetTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                    categoryNonLobbyingTotal = new BigDecimal(0.0);
                    categoryNonLobbyingTotal = categoryNonLobbyingTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                    categoryActualLobbyingTotal = new BigDecimal(0.0);
                    categoryActualLobbyingTotal = categoryActualLobbyingTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                    categoryActualTotal = new BigDecimal(0.0);
                    categoryActualTotal = categoryActualTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);

                    currentCategory = information.get(i);
                }
                if (!information.get(i).equals("-")) {
                    if (Query.isSubcategory(conn, information.get(i))) {
                        table.addCell(subcategoryCell(information.get(i)));

                        BigDecimal budget = new BigDecimal(Query.getSubcategoryBudgetValue(conn, information.get(i), year));
                        budget = budget.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                        BigDecimal nonLobbying = new BigDecimal(Query.subcategoryExpenseActualValueAsOfDate(conn, information.get(i), year, date));
                        nonLobbying = nonLobbying.setScale(2, BigDecimal.ROUND_HALF_EVEN);

                        BigDecimal lobbying = new BigDecimal(0.0);
                        lobbying = lobbying.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                        if (Query.getLobbyingExpense(conn, information.get(i)) > -1) {
                            BigDecimal percentage = new BigDecimal(Query.getLobbyingExpense(conn, information.get(i)));
                            percentage = percentage.multiply(new BigDecimal(0.01));
                            lobbying = nonLobbying.multiply(percentage);
                            lobbying = lobbying.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                        }

                        table.addCell(regAmountCell(String.valueOf(budget)));

                        if (!(lobbying.doubleValue() > 0.0)) {
                            table.addCell(regAmountCell(String.valueOf(nonLobbying)));
                            table.addCell(emptyCell(""));
                            table.addCell(regAmountCell(String.valueOf(nonLobbying)));
                        } else {
                            table.addCell(regAmountCell(String.valueOf(nonLobbying.subtract(lobbying))));
                            table.addCell(regAmountCell(String.valueOf(lobbying)));
                            table.addCell(regAmountCell(String.valueOf(nonLobbying)));

                            categoryActualLobbyingTotal = categoryActualLobbyingTotal.add(lobbying);
                            sectionActualLobbyingTotal = sectionActualLobbyingTotal.add(lobbying);
                        }

                        sectionBudgetTotal = sectionBudgetTotal.add(budget);
                        categoryBudgetTotal = categoryBudgetTotal.add(budget);

                        sectionNonLobbyingTotal = sectionNonLobbyingTotal.add(nonLobbying);
                        categoryNonLobbyingTotal = categoryNonLobbyingTotal.add(nonLobbying);
                    } else {
                        if (!Query.hasSubcategory(conn, currentCategory)) {
                            table.addCell(categoryCell(information.get(i)));

                            BigDecimal budget = new BigDecimal(Query.getCategoryBudgetValue(conn, information.get(i), year));
                            budget = budget.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                            BigDecimal nonLobbying = new BigDecimal(Query.categoryExpenseActualValueAsOfDate(conn, information.get(i), year, date));
                            nonLobbying = nonLobbying.setScale(2, BigDecimal.ROUND_HALF_EVEN);

                            table.addCell(regAmountCell(String.valueOf(budget)));
                            table.addCell(regAmountCell(String.valueOf(nonLobbying)));
                            table.addCell(emptyCell(""));
                            table.addCell(emptyCell(""));
                        } else {
                            table.addCell(categoryCell(information.get(i)));
                            table.addCell(emptyCell(""));
                            table.addCell(emptyCell(""));
                            table.addCell(emptyCell(""));
                            table.addCell(emptyCell(""));
                        }
                    }
                    table.addCell(lobbyingCell(""));
                }
            }

            if (Query.hasSubcategory(conn, currentCategory)) {
                table.addCell(categoryTotalCell("TOTAL " + currentCategory));

                table.addCell(totalAmountCell(String.valueOf(categoryBudgetTotal)));
                table.addCell(totalAmountCell(String.valueOf(categoryNonLobbyingTotal)));

                if (categoryActualLobbyingTotal.doubleValue() > 0.0) {
                    table.addCell(totalAmountCell(String.valueOf(categoryActualLobbyingTotal)));
                } else {
                    table.addCell(totalAmountCell("-"));
                }
                table.addCell(totalAmountCell(String.valueOf(categoryActualLobbyingTotal.add(categoryNonLobbyingTotal))));

                BigDecimal total = sectionNonLobbyingTotal.add(sectionActualLobbyingTotal);
                BigDecimal lobbying = sectionActualLobbyingTotal.divide(total, 2, BigDecimal.ROUND_HALF_DOWN);

                table.addCell(lobbyingCell(String.valueOf(lobbying)));
            }

            // Total expenses
            table.addCell(sectionCell("TOTAL EXPENSES"));
            table.addCell(totalAmountCell(String.valueOf(sectionBudgetTotal)));
            table.addCell(totalAmountCell(String.valueOf(sectionNonLobbyingTotal)));

            if (sectionActualLobbyingTotal.doubleValue() > 0.0) {
                table.addCell(totalAmountCell(String.valueOf(sectionActualLobbyingTotal)));
            } else {
                table.addCell(totalAmountCell("-"));
            }
            table.addCell(totalAmountCell(String.valueOf(sectionNonLobbyingTotal.add(sectionActualLobbyingTotal))));
            table.addCell(lobbyingCell("total lobbying"));

            // Empty Row
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(lobbyingCell(""));

            // Display income information
            table.addCell(sectionCell("INCOME"));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(lobbyingCell(""));

            sectionBudgetTotal = new BigDecimal(0.0);
            sectionBudgetTotal = sectionBudgetTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);
            sectionNonLobbyingTotal = new BigDecimal(0.0);
            sectionNonLobbyingTotal = sectionNonLobbyingTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);
            sectionActualLobbyingTotal = new BigDecimal(0.0);
            sectionActualLobbyingTotal = sectionActualLobbyingTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);
            sectionActualTotal = new BigDecimal(0.0);
            sectionActualTotal = sectionActualTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);

            categoryBudgetTotal = new BigDecimal(0.0);
            categoryBudgetTotal = categoryBudgetTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);
            categoryNonLobbyingTotal = new BigDecimal(0.0);
            categoryNonLobbyingTotal = categoryNonLobbyingTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);
            categoryActualLobbyingTotal = new BigDecimal(0.0);
            categoryActualLobbyingTotal = categoryActualLobbyingTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);
            categoryActualTotal = new BigDecimal(0.0);
            categoryActualTotal = categoryActualTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);

            information = Query.getAllIncomeCategoriesAndSubcategories(conn);
            currentCategory = information.get(0);
            for (int i = 0; i < information.size(); i++) {
                if (Query.isCategory(conn, information.get(i)) && !information.get(i).equals(currentCategory)) {
                    if (Query.hasSubcategory(conn, currentCategory)) {
                        table.addCell(categoryTotalCell("TOTAL " + currentCategory));

                        table.addCell(totalAmountCell(String.valueOf(categoryBudgetTotal)));
                        table.addCell(totalAmountCell(String.valueOf(categoryNonLobbyingTotal)));

                        if (categoryActualLobbyingTotal.doubleValue() > 0.0) {
                            table.addCell(totalAmountCell(String.valueOf(categoryActualLobbyingTotal)));
                        } else {
                            table.addCell(totalAmountCell("-"));
                        }
                        table.addCell(totalAmountCell(String.valueOf(categoryActualLobbyingTotal.add(categoryNonLobbyingTotal))));

                        table.addCell(lobbyingCell(""));
                    }

                    categoryBudgetTotal = new BigDecimal(0.0);
                    categoryBudgetTotal = categoryBudgetTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                    categoryNonLobbyingTotal = new BigDecimal(0.0);
                    categoryNonLobbyingTotal = categoryNonLobbyingTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                    categoryActualLobbyingTotal = new BigDecimal(0.0);
                    categoryActualLobbyingTotal = categoryActualLobbyingTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                    categoryActualTotal = new BigDecimal(0.0);
                    categoryActualTotal = categoryActualTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);

                    currentCategory = information.get(i);
                }
                if (!information.get(i).equals("-")) {
                    if (Query.isSubcategory(conn, information.get(i))) {
                        table.addCell(subcategoryCell(information.get(i)));

                        BigDecimal budget = new BigDecimal(Query.getSubcategoryBudgetValue(conn, information.get(i), year));
                        budget = budget.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                        BigDecimal nonLobbying = new BigDecimal(Query.subcategoryIncomeActualValueAsOfDate(conn, information.get(i), year, date));
                        nonLobbying = nonLobbying.setScale(2, BigDecimal.ROUND_HALF_EVEN);

                        BigDecimal lobbying = new BigDecimal(0.0);
                        lobbying = lobbying.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                        if (Query.getLobbyingExpense(conn, information.get(i)) > -1) {
                            BigDecimal percentage = new BigDecimal(Query.getLobbyingExpense(conn, information.get(i)));
                            percentage = percentage.multiply(new BigDecimal(0.01));
                            lobbying = nonLobbying.multiply(percentage);
                            lobbying = lobbying.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                        }

                        table.addCell(regAmountCell(String.valueOf(budget)));

                        if (!(lobbying.doubleValue() > 0.0)) {
                            table.addCell(regAmountCell(String.valueOf(nonLobbying)));
                            table.addCell(emptyCell(""));
                            table.addCell(regAmountCell(String.valueOf(nonLobbying)));
                        } else {
                            table.addCell(regAmountCell(String.valueOf(nonLobbying.subtract(lobbying))));
                            table.addCell(regAmountCell(String.valueOf(lobbying)));
                            table.addCell(regAmountCell(String.valueOf(nonLobbying)));

                            categoryActualLobbyingTotal = categoryActualLobbyingTotal.add(lobbying);
                            sectionActualLobbyingTotal = sectionActualLobbyingTotal.add(lobbying);
                        }

                        sectionBudgetTotal = sectionBudgetTotal.add(budget);
                        categoryBudgetTotal = categoryBudgetTotal.add(budget);

                        sectionNonLobbyingTotal = sectionNonLobbyingTotal.add(nonLobbying);
                        categoryNonLobbyingTotal = categoryNonLobbyingTotal.add(nonLobbying);
                    } else {
                        if (!Query.hasSubcategory(conn, currentCategory)) {
                            table.addCell(categoryCell(information.get(i)));

                            BigDecimal budget = new BigDecimal(Query.getCategoryBudgetValue(conn, information.get(i), year));
                            budget = budget.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                            BigDecimal nonLobbying = new BigDecimal(Query.categoryIncomeActualValueAsOfDate(conn, information.get(i), year, date));
                            nonLobbying = nonLobbying.setScale(2, BigDecimal.ROUND_HALF_EVEN);

                            table.addCell(regAmountCell(String.valueOf(budget)));
                            table.addCell(regAmountCell(String.valueOf(nonLobbying)));
                            table.addCell(emptyCell(""));
                            table.addCell(emptyCell(""));
                        } else {
                            table.addCell(categoryCell(information.get(i)));
                            table.addCell(emptyCell(""));
                            table.addCell(emptyCell(""));
                            table.addCell(emptyCell(""));
                            table.addCell(emptyCell(""));
                        }
                    }
                    table.addCell(lobbyingCell(""));
                }
            }

            if (Query.hasSubcategory(conn, currentCategory)) {
                table.addCell(categoryTotalCell("TOTAL " + currentCategory));

                table.addCell(totalAmountCell(String.valueOf(categoryBudgetTotal)));
                table.addCell(totalAmountCell(String.valueOf(categoryNonLobbyingTotal)));

                if (categoryActualLobbyingTotal.doubleValue() > 0.0) {
                    table.addCell(totalAmountCell(String.valueOf(categoryActualLobbyingTotal)));
                } else {
                    table.addCell(totalAmountCell("-"));
                }
                table.addCell(totalAmountCell(String.valueOf(categoryActualLobbyingTotal.add(categoryNonLobbyingTotal))));

                table.addCell(lobbyingCell(""));
            }

            // Total Income
            table.addCell(sectionCell("TOTAL INCOME"));
            table.addCell(totalAmountCell(String.valueOf(sectionBudgetTotal)));
            table.addCell(totalAmountCell(String.valueOf(sectionNonLobbyingTotal)));

            if (sectionActualLobbyingTotal.doubleValue() > 0.0) {
                table.addCell(totalAmountCell(String.valueOf(sectionActualLobbyingTotal)));
            } else {
                table.addCell(totalAmountCell("-"));
            }
            table.addCell(totalAmountCell(String.valueOf(sectionNonLobbyingTotal.add(sectionActualLobbyingTotal))));
            table.addCell(lobbyingCell(""));

            d.add(table);

            d.close();

            File file = new File(filename);
            Desktop.getDesktop().open(file);
        } catch (IOException ex) {
            Logger.getLogger(MonthlyReport.class.getName()).log(Level.SEVERE, null, ex);
        } catch (DocumentException ex) {
            Logger.getLogger(MonthlyReport.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private static PdfPCell totalAmountCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.BOLD);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setNoWrap(false);
        cell.setFixedHeight(15f);
        cell.setBorderWidthTop(2f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_RIGHT);
        return cell;
    }

    private static PdfPCell regAmountCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setNoWrap(false);
        cell.setFixedHeight(15f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_RIGHT);
        return cell;
    }

    private static PdfPCell sectionCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.BOLD);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setFixedHeight(15f);
        return cell;
    }

    private static PdfPCell emptyCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setFixedHeight(15f);
        return cell;
    }

    private static PdfPCell categoryCell(String s) {
        s = tab + s;
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setFixedHeight(15f);
        return cell;
    }

    private static PdfPCell subcategoryCell(String s) {
        s = tab + tab + s;
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setFixedHeight(15f);
        return cell;
    }

    private static PdfPCell categoryTotalCell(String s) {
        s = tab + tab + tab + s;
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setFixedHeight(15f);
        return cell;
    }

    private static PdfPCell headerCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.BOLD);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setNoWrap(false);
        cell.setFixedHeight(15f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        return cell;
    }

    private static PdfPCell lobbyingCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.BOLD);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setNoWrap(false);
        cell.setBorder(0);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        return cell;
    }
}
